package com.socialrss.utils;
import com.socialrss.accounts.AbstractHolder;
import com.socialrss.accounts.AccountOwner;
import com.socialrss.accounts.FollowedPerson;
import com.socialrss.accounts.Group;
import com.socialrss.accounts.InstagramNotification;
import com.socialrss.accounts.InstagramUser;
import com.socialrss.accounts.dbinterfaces.*;
import com.socialrss.accounts.factoryobject.ConnectionFactory;
import com.socialrss.accounts.factoryobject.GroupFactory;
import com.socialrss.accounts.factoryobject.InstagramNotificationFactory;
import com.socialrss.accounts.factoryobject.TwitterNotificationFactory;
import com.socialrss.constants.SocialNetworkNames;
import com.socialrss.constants.SocialNetworkNames.SocialNetKeys;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class SocialRssModel extends SQLiteOpenHelper{
	
	public static SocialRssModel _socialrssmmodel;
	
	private static final String DB_NAME="connections.db";
	private static final int DB_VERSION=1;
	private static final String SRM_LOG_ERR = "SocialRssModel Error";
	
	private LinkedHashMap<String, LinkedHashMap<Integer,AbstractHolder>> _socialNetworksWithConnections;
	private LinkedHashMap<Integer,Group> _allCategories;
	private ArrayList<Group> _outerCategories;
	private ArrayList<FollowedPerson> _allConnections;
	private SQLiteDatabase _readableDB, _writableDB;
	
	public SocialRssModel(Context con){
		super(con, DB_NAME, null, DB_VERSION);
		this.getDatabaseReady();
		loadHolders();
	}
	
	public static SocialRssModel Instance(Context con){
		if(_socialrssmmodel == null){
			if(con == null){
				Log.e("SocialRss Model", "SocialRssModel requires context");
				System.exit(0);
			}
			_socialrssmmodel = new SocialRssModel(con);
		}
		return _socialrssmmodel;
	}
	/**
	 * It loads all holders from database
	 */
	public void loadHolders(){
		_allCategories = new LinkedHashMap<Integer, Group>();
		_outerCategories = new ArrayList<Group>();
		_allConnections = new ArrayList<FollowedPerson>();
		
		_socialNetworksWithConnections = new LinkedHashMap<String, LinkedHashMap<Integer,AbstractHolder>>();
		
		/*
		 * Fetch data from database
		 */
		
		_socialNetworksWithConnections.put(SocialNetworkNames.TWITTER, new LinkedHashMap<Integer,AbstractHolder>());
		_socialNetworksWithConnections.put(SocialNetworkNames.FACEBOOK, new LinkedHashMap<Integer,AbstractHolder>());
		_socialNetworksWithConnections.put(SocialNetworkNames.INSTAGRAM, new LinkedHashMap<Integer,AbstractHolder>());
		_socialNetworksWithConnections.put(SocialNetworkNames.GOOGLE, new LinkedHashMap<Integer,AbstractHolder>());
		_socialNetworksWithConnections.put(SocialNetworkNames.FOURSQUARE, new LinkedHashMap<Integer,AbstractHolder>());
		
		this.fetchData();
	}
	/**
	 * socialnet parameter will be equal to "", if social network is not known
	 */
	public AbstractHolder getAbstractHolder(Integer id, String socialnet, boolean isCategory){
		if(isCategory)								//if it is a category, then get it from category list
			return allCategories().get(id);
		else{										//if it is a person, then get it
			if(socialnet.equalsIgnoreCase("")){		//if social network is not known
				AbstractHolder tempobj;
				Iterator<String> keys = socialNetworksWithConnections().keySet().iterator();
				while(keys.hasNext()){
					socialnet = keys.next();
					tempobj = socialNetworksWithConnections().get(socialnet).get(id);
					if(tempobj!=null)
						return tempobj;
				}
				return null;
			}
			else{									//if social network is known
				return socialNetworksWithConnections().get(socialnet).get(id);
			}
		}
	}
	/**
	 * Insert a connection into hash map of its social network
	 * 
	 * @param temp
	 * @param socialnet
	 */
	public void putAbstractHolder(AbstractHolder temp, String socialnet){
		socialNetworksWithConnections().get(socialnet).put(temp.getId(), temp);
	}
	public void fetchData(){
		
		//fetch facebook data
		String query = "SELECT " + ConnectionTable._ID + "," + ConnectionTable.NAME +"," + ConnectionTable.SOCIAL_AREA +"," + 
				ConnectionTable.SELECTED + "," + ConnectionTable.USERID + "," + InGroupTable.GROUP_ID +
				" FROM " + ConnectionTable.TABLE_NAME + " LEFT JOIN " + InGroupTable.TABLE_NAME + " ON " + ConnectionTable._ID + "= " + InGroupTable.CONN_ID +
				" WHERE " + ConnectionTable.SOCIAL_AREA + " = '" + SocialNetworkNames.SocialNetKeys.fa.toString() + "'";
		Cursor cur = _readableDB.rawQuery(query, null);
		
		ConnectionFactory connFac = new ConnectionFactory();
		GroupFactory groupFac = new GroupFactory();
		LinkedHashMap<Integer, AbstractHolder> face = _socialNetworksWithConnections.get(SocialNetworkNames.FACEBOOK);
		AbstractHolder temp;
		if(cur.moveToFirst()){
			do{
				temp = connFac.turnToObj(cur);
				face.put(temp.getId(), temp);
				_allConnections.add((FollowedPerson)temp);
			}while(cur.moveToNext());
			cur.close();
		}
		//fetch twitter data
		query = "SELECT " + ConnectionTable._ID + "," + ConnectionTable.NAME +"," + ConnectionTable.SOCIAL_AREA +"," + 
				ConnectionTable.SELECTED + "," + ConnectionTable.USERID + "," + InGroupTable.GROUP_ID +
				" FROM " + ConnectionTable.TABLE_NAME + " LEFT JOIN " + InGroupTable.TABLE_NAME + " ON " + ConnectionTable._ID + "= " + InGroupTable.CONN_ID +
				" WHERE " + ConnectionTable.SOCIAL_AREA + " = '" + SocialNetworkNames.SocialNetKeys.tw.toString() + "'";
		cur = _readableDB.rawQuery(query, null);
		
		LinkedHashMap<Integer, AbstractHolder> twit = _socialNetworksWithConnections.get(SocialNetworkNames.TWITTER);
		if(cur.moveToFirst()){
			do{
				temp = connFac.turnToObj(cur);
				twit.put(temp.getId(), temp);
				_allConnections.add((FollowedPerson)temp);
			}while(cur.moveToNext());
			cur.close();
		}
		
		//fetch google+ data
		query = "SELECT " + ConnectionTable._ID + "," + ConnectionTable.NAME +"," + ConnectionTable.SOCIAL_AREA +"," + 
				ConnectionTable.SELECTED + "," + ConnectionTable.USERID + "," + InGroupTable.GROUP_ID +
				" FROM " + ConnectionTable.TABLE_NAME + " LEFT JOIN " + InGroupTable.TABLE_NAME + " ON " + ConnectionTable._ID + "= " + InGroupTable.CONN_ID +
				" WHERE " + ConnectionTable.SOCIAL_AREA + " = '" + SocialNetworkNames.SocialNetKeys.g_.toString() + "'";
		cur = _readableDB.rawQuery(query, null);
		
		LinkedHashMap<Integer, AbstractHolder> google = _socialNetworksWithConnections.get(SocialNetworkNames.GOOGLE);
		if(cur.moveToFirst()){
			do{
				temp = connFac.turnToObj(cur);
				google.put(temp.getId(), temp);
				_allConnections.add((FollowedPerson)temp);
			}while(cur.moveToNext());
			cur.close();
		}
		//fetch foursquare data
		query = "SELECT " + ConnectionTable._ID + "," + ConnectionTable.NAME +"," + ConnectionTable.SOCIAL_AREA +"," + 
				ConnectionTable.SELECTED + "," + ConnectionTable.USERID + "," + InGroupTable.GROUP_ID +
				" FROM " + ConnectionTable.TABLE_NAME + " LEFT JOIN " + InGroupTable.TABLE_NAME + " ON " + ConnectionTable._ID + "= " + InGroupTable.CONN_ID +
				" WHERE " + ConnectionTable.SOCIAL_AREA + " = '" + SocialNetworkNames.SocialNetKeys.f_sq.toString() + "'";
		cur = _readableDB.rawQuery(query, null);
		
		LinkedHashMap<Integer, AbstractHolder> foursquare = _socialNetworksWithConnections.get(SocialNetworkNames.FOURSQUARE);
		if(cur.moveToFirst()){
			do{
				temp = connFac.turnToObj(cur);
				foursquare.put(temp.getId(), temp);
				_allConnections.add((FollowedPerson)temp);
			}while(cur.moveToNext());
			cur.close();
		}
		
		//fetch instagram data
		query = "SELECT " + ConnectionTable._ID + "," + ConnectionTable.NAME +"," + ConnectionTable.SOCIAL_AREA +"," + 
				ConnectionTable.SELECTED + "," + ConnectionTable.USERID + "," + InGroupTable.GROUP_ID +
				" FROM " + ConnectionTable.TABLE_NAME + " LEFT JOIN " + InGroupTable.TABLE_NAME + " ON " + ConnectionTable._ID + "= " + InGroupTable.CONN_ID +
				" WHERE " + ConnectionTable.SOCIAL_AREA + " = '" + SocialNetworkNames.SocialNetKeys.ins.toString() + "'";
		cur = _readableDB.rawQuery(query, null);
		
		LinkedHashMap<Integer, AbstractHolder> ins = _socialNetworksWithConnections.get(SocialNetworkNames.INSTAGRAM);
		if(cur.moveToFirst()){
			do{
				temp = connFac.turnToObj(cur);
				ins.put(temp.getId(), temp);
				_allConnections.add((FollowedPerson)temp);
			}while(cur.moveToNext());
			cur.close();
		}
		
		//fetch groups
		query= "SELECT " + GroupTable._ID + "," + GroupTable.NAME + "," + GroupTable.SELECTED + "," + InGroupTable.GROUP_ID +
				" FROM " + GroupTable.TABLE_NAME + " LEFT JOIN " + InGroupTable.TABLE_NAME + " ON " + GroupTable._ID + "= " + InGroupTable.CONN_ID;
		cur = _readableDB.rawQuery(query, null);
		if(cur.moveToFirst()){
			do{
				temp = groupFac.turnToObj(cur);
				_allCategories.put(temp.getId(), (Group)temp);
				if(((Group)temp).isMostOuter()){
					outerCategories().add((Group)temp);
				}
			}while(cur.moveToNext());
			cur.close();
		}
	}
	/*********************************************/
	/****Social Network Methods*******************/
	/*********************************************/
	public void instagramAuth(){
		
	}
	/**
	 * Insert a category
	 * 
	 * @param temp
	 * @param isOuter
	 */
	public void putCategory(Group temp, boolean isOuter){
		allCategories().put(temp.getId(), temp);
		if(isOuter)
			outerCategories().add(temp);
	}
	/*
	 * SQLiteOpenHelper methods are overriden
	 * 
	 */
	@Override
	public void onCreate(SQLiteDatabase db){
		//Connections table is created
		db.execSQL("CREATE TABLE "+ ConnectionTable.TABLE_NAME+ " (" + ConnectionTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ ConnectionTable.NAME + " TEXT NOT NULL, " 
				+ ConnectionTable.SOCIAL_AREA + " TEXT NOT NULL,"
				+ ConnectionTable.SELECTED + " INTEGER NOT NULL,"
				+ ConnectionTable.USERID + " TEXT NOT NULL);");
		
		//Groups table is created
		db.execSQL("CREATE TABLE "+ GroupTable.TABLE_NAME+ " (" + GroupTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ GroupTable.NAME + " TEXT NOT NULL, " + GroupTable.SELECTED + " INTEGER NOT NULL);");
		
		//Ingroup table is created
		db.execSQL("CREATE TABLE "+ InGroupTable.TABLE_NAME+ " (" + InGroupTable.GROUP_ID + " INTEGER NOT NULL, " + 
					InGroupTable.CONN_ID + " INTEGER NOT NULL," +
					" FOREIGN KEY (" + InGroupTable.GROUP_ID + ") REFERENCES " + GroupTable.TABLE_NAME + " (" +GroupTable._ID+ ") ON DELETE CASCADE," +
					" FOREIGN KEY (" + InGroupTable.CONN_ID + ") REFERENCES " + ConnectionTable.TABLE_NAME + " (" +ConnectionTable._ID+ ") ON DELETE CASCADE);");
		//SocialNetworkTokensTable
		db.execSQL("CREATE TABLE "+ SocialNetworkTokensTable.TABLE_NAME+ " (" + 
				SocialNetworkTokensTable.NETWORK_NAME + " TEXT PRIMARY KEY NOT NULL, " + 
				SocialNetworkTokensTable.REQUEST_TOKEN + " TEXT ," +
				SocialNetworkTokensTable.ACCESS_TOKEN + " TEXT NOT NULL," +
				SocialNetworkTokensTable.USERID + " INTEGER NOT NULL,"+
				SocialNetworkTokensTable.USERNAME + " TEXT NOT NULL,"+ 
				SocialNetworkTokensTable.FULLNAME + " TEXT);");	
		
		//LastPostInFeedTable
		db.execSQL("CREATE TABLE "+ ILastPostInFeedTable.TABLE_NAME+ " (" + ILastPostInFeedTable.SOCIAL_NET_NAME + " TEXT PRIMARY KEY, "
				+ ILastPostInFeedTable.POST_ID + " TEXT NOT NULL);");
		
		//InstagramNotificationTable
		db.execSQL("CREATE TABLE "+ IInstagramNotificationTable.TABLE_NAME+ " (" 
				+ IInstagramNotificationTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ IInstagramNotificationTable.USER_ID + " TEXT NOT NULL, "
				+ IInstagramNotificationTable.POST_LINK + " TEXT NOT NULL,"
				+ IInstagramNotificationTable.IMAGE_LINK + " TEXT NOT NULL, "
				+ IInstagramNotificationTable.POST_DATE + " INTEGER NOT NULL, "
				+ " FOREIGN KEY (" + IInstagramNotificationTable.USER_ID + ") REFERENCES " +
					ConnectionTable.TABLE_NAME + " (" +ConnectionTable.USERID+ ") ON DELETE CASCADE);");
		
		//TwitterNotificationTable
		db.execSQL("CREATE TABLE "+ ITwitterNotificationTable.TABLE_NAME+ " (" 
				+ ITwitterNotificationTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ ITwitterNotificationTable.USER_ID + " TEXT NOT NULL, "
				+ ITwitterNotificationTable.TWIT_CONTENT + " TEXT NOT NULL,"
				+ ITwitterNotificationTable.TWIT_LINK + " TEXT NOT NULL, "
				+ ITwitterNotificationTable.TWIT_DATE + " INTEGER NOT NULL, "
				+ " FOREIGN KEY (" + ITwitterNotificationTable.USER_ID + ") REFERENCES " +
					ConnectionTable.TABLE_NAME + " (" +ConnectionTable.USERID+ ") ON DELETE CASCADE);");
//		//after version 7
		db.execSQL("CREATE TRIGGER account_is_removed AFTER DELETE ON " + SocialNetworkTokensTable.TABLE_NAME +
				" FOR EACH ROW " + 
				"BEGIN " +
				"DELETE FROM " + ConnectionTable.TABLE_NAME + " WHERE " + ConnectionTable.SOCIAL_AREA + "= old.netname;" +
				"END;");
	}
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldversion, int newversion){
		//TwitterNotificationTable
		db.execSQL("CREATE TABLE  IF EXISTS "+ ITwitterNotificationTable.TABLE_NAME);
		db.execSQL("CREATE TABLE "+ ITwitterNotificationTable.TABLE_NAME+ " (" 
				+ ITwitterNotificationTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ ITwitterNotificationTable.USER_ID + " TEXT NOT NULL, "
				+ ITwitterNotificationTable.TWIT_CONTENT + " TEXT NOT NULL,"
				+ ITwitterNotificationTable.TWIT_LINK + " TEXT NOT NULL, "
				+ ITwitterNotificationTable.TWIT_DATE + " INTEGER NOT NULL, "
				+ " FOREIGN KEY (" + ITwitterNotificationTable.USER_ID + ") REFERENCES " +
					ConnectionTable.TABLE_NAME + " (" +ConnectionTable.USERID+ ") ON DELETE CASCADE);");
		
	
	}
	/*******************************************************************/
	/*******************************************************************/
	/***********************DATABASE QUERIES****************************/
	/*******************************************************************/
	/*******************************************************************/
	/**
	 * This method sets a lower bound for ids of connections.
	 * We want autoincrement to start from 5000000
	 */
	private void getDatabaseReady(){
		_readableDB = this.getReadableDatabase();
		_writableDB = this.getWritableDatabase();
		
		long count = DatabaseUtils.queryNumEntries(_readableDB, ConnectionTable.TABLE_NAME);
		if(count < 1){							//set an lower limit for connection id
			int id = 5000000;
			ContentValues values = new ContentValues();
			values.put(ConnectionTable.NAME, "Django");
			values.put(ConnectionTable.SOCIAL_AREA, SocialNetworkNames.SocialNetKeys.fa.toString());
			values.put(ConnectionTable.SELECTED, 0);
			values.put(ConnectionTable.USERID, 0);
			values.put(ConnectionTable._ID, id);
			_writableDB.insertOrThrow(ConnectionTable.TABLE_NAME, null, values);
			

			String where = ConnectionTable._ID + " = ? ";
			_writableDB.delete(ConnectionTable.TABLE_NAME, where, new String[] {String.valueOf(id)});
		}
	}
	/**
	 * Insert request token for a specific social network into socialnetworktokens table
	 * @param netw SocialNetKeys
	 * @param token
	 */
	public void addNewSocialNetworkReqToken(String netw, String reqtoken, String accesstoken, String username, String userid, String fullname){
		ContentValues values = new ContentValues();
		if(netw.equals("") || reqtoken.equals("") || accesstoken.equals("") || username.equals("") || userid.equals("")){
			Log.e("Social_Network_Auth", "Parameters couldn't be empty, except fullname");
			System.exit(0);
		}
		values.put(SocialNetworkTokensTable.NETWORK_NAME, netw);
		values.put(SocialNetworkTokensTable.REQUEST_TOKEN, reqtoken);
		values.put(SocialNetworkTokensTable.ACCESS_TOKEN, accesstoken);
		values.put(SocialNetworkTokensTable.USERID, userid);
		values.put(SocialNetworkTokensTable.USERNAME, username);
		if(fullname.length() > 0)
			values.put(SocialNetworkTokensTable.FULLNAME, fullname);
		_writableDB.insertOrThrow(SocialNetworkTokensTable.TABLE_NAME, null, values);
	}
	/**
	 * Get request token if it exists
	 * @param netw SocialNetKeys
	 * @return
	 */
	public AccountOwner getAccessTokenOfSocialNet(String netw){
		AccountOwner token = new AccountOwner();
		Cursor cur = _readableDB.query(SocialNetworkTokensTable.TABLE_NAME, new String[]{SocialNetworkTokensTable.ACCESS_TOKEN, SocialNetworkTokensTable.USERID, SocialNetworkTokensTable.REQUEST_TOKEN}, 
				SocialNetworkTokensTable.NETWORK_NAME + "='" + netw +"'", null, null, null, null);
		if(cur.moveToFirst()){
				token.setAccessToken(cur.getString(0));
				token.setUserId(cur.getString(1));
				token.setSecret_token(cur.getString(2));
		}
		cur.close();
		return token;
	}
	/**
	 * Get request token if it exists
	 * @param netw SocialNetKeys
	 * @return
	 */
	public void removeSocialAccount(String netw){

		String where = SocialNetworkTokensTable.NETWORK_NAME + " = ?";
		_writableDB.delete(SocialNetworkTokensTable.TABLE_NAME, where, new String[] {netw});
	}
	/**
	 * Set id of last post in social network
	 * @param sname	SocialNetKeys.toString()
	 * @param pid	String
	 */
	public void setLastPostId(String sname, String pid){
		String strFilter = ILastPostInFeedTable.SOCIAL_NET_NAME + "='" + sname + "'";
		ContentValues args = new ContentValues();
		args.put(ILastPostInFeedTable.POST_ID, pid);
		_writableDB.update(ILastPostInFeedTable.TABLE_NAME, args, strFilter, null);
	}
	/**
	 * Insert id of last post in social network
	 * @param sname	SocialNetKeys.toString()
	 * @param pid	String
	 */
	public void insertLastPostId(String sname, String pid){
		ContentValues args = new ContentValues();
		args.put(ILastPostInFeedTable.POST_ID, pid);
		args.put(ILastPostInFeedTable.SOCIAL_NET_NAME, sname);
		_writableDB.insertOrThrow(ILastPostInFeedTable.TABLE_NAME, null, args);
	}

	/**
	 * Get Id of Last Post in a social network
	 * @param sname	SocialNetKeys.toSring()
	 * @return
	 */
	public String getLastPostId(String sname){
		Cursor cur = _readableDB.query(ILastPostInFeedTable.TABLE_NAME, new String[]{ILastPostInFeedTable.POST_ID}, 
				ILastPostInFeedTable.SOCIAL_NET_NAME + "='" + sname + "'", null, null, null, null);
		String value = null;
		if(cur.moveToFirst())
			value = cur.getString(0);
		cur.close();
		return value;
	}
	
	public int removeGroup(int gid){
		String where = GroupTable._ID + " = ?";
		return _writableDB.delete(GroupTable.TABLE_NAME, where, new String[] {String.valueOf(gid)});
	}
	/**
	 * It queries and returns number of contents in this group.
	 * @param gid
	 * @return
	 */
	public int getNumOfContentsInGroup(int gid){
		Cursor mCount= _readableDB.rawQuery("SELECT COUNT(*) FROM "+ InGroupTable.TABLE_NAME +"" +
				" WHERE "+ InGroupTable.GROUP_ID +"=" + gid, null);
		mCount.moveToFirst();
		int count= mCount.getInt(0);
		mCount.close();
		return count;
	}
	/**
	 * When group name is updated, its db value is set, too
	 * @param gid
	 * @param name
	 */
	public void groupNameIsUpdated(int gid, String name){
		Group temp = _allCategories.get(gid);
		temp.setName(name);
		String strFilter = GroupTable._ID + "=" + gid;
		ContentValues args = new ContentValues();
		args.put(GroupTable.NAME, name);
		_writableDB.update(GroupTable.TABLE_NAME, args, strFilter, null);
	}
	/**
	 * When turned on property of the group is updated, 
	 * do necessary changes in db, too.
	 * @param gid
	 */
	public void groupTurnedOnIsUpdated(int gid){
		Group temp = _allCategories.get(gid);
		String strFilter = GroupTable._ID + "=" + gid;
		ContentValues args = new ContentValues();
		args.put(GroupTable.SELECTED, temp.isTurnedOn());
		_writableDB.update(GroupTable.TABLE_NAME, args, strFilter, null);
	}
	public void updateGroup(Group gr, String...columns){
		GroupFactory fact = new GroupFactory();
		ContentValues vals = fact.turnToPartialDBInput(gr, columns);
		String strFilter = GroupTable._ID + "=" + gr.getId();
		_writableDB.update(GroupTable.TABLE_NAME, vals, strFilter, null);
	}
	/**
	 * It fetches the ids of holders inside the group with a given id
	 * @param gid
	 * @return
	 */
	public LinkedHashMap<Integer, AbstractHolder> getContentOfGroup(int gid){
		LinkedHashMap<Integer, AbstractHolder> result = new LinkedHashMap<Integer, AbstractHolder>();
		Cursor cur = _readableDB.query(InGroupTable.TABLE_NAME, new String[]{InGroupTable.CONN_ID}, 
				InGroupTable.GROUP_ID + "=" + gid, null, null, null, null);
		if(cur.moveToFirst()){
			do{
				result.put(cur.getInt(0), null);
			}while(cur.moveToNext());
		}
		cur.close();
		return result;
	}
	/**
	 * Insert a new row into InGroup Table
	 * @param gid
	 * @param cid
	 */
	public void addNewRowInTo_InGroupTable(int gid, int cid){
		ContentValues values = new ContentValues();
		values.put(InGroupTable.GROUP_ID, gid);
		values.put(InGroupTable.CONN_ID, cid);
		_writableDB.insertOrThrow(InGroupTable.TABLE_NAME, null, values);
	}
	/**
	 * Delete a row in InGroup Table
	 * @param gid
	 * @param cid
	 */
	public void deleteRow_InGroupTable(int gid, int cid){
		String where = InGroupTable.GROUP_ID + " = ? AND " + InGroupTable.CONN_ID + " = ?";
		_writableDB.delete(InGroupTable.TABLE_NAME, where, new String[] {String.valueOf(gid), String.valueOf(cid)});
	}
	
	/**
	 * It inserts a new group into database
	 * @param name
	 * @return
	 */
	public boolean insertNewGroup(String name){
		Cursor cur = _readableDB.rawQuery("SELECT COUNT(*) FROM "+ GroupTable.TABLE_NAME +"" +
				" WHERE "+ GroupTable.NAME +"='" + name +"'", null);
		cur.moveToFirst();
		int count= cur.getInt(0);
		cur.close();
		if(count > 0)				//if there is a group with same name, don't insert a group
			return false;
		
		ContentValues values = new ContentValues();
		values.put(GroupTable.NAME, name);
		values.put(GroupTable.SELECTED, 0);
		_writableDB.insertOrThrow(GroupTable.TABLE_NAME, null, values);
		
		return true;
	}
	/**
	 * Returns id of a group whose name is given
	 * @param name
	 * @return
	 */
	public int getIdOfGroup(String name){
		Cursor cur = _readableDB.query(GroupTable.TABLE_NAME, new String[]{GroupTable._ID}, 
				GroupTable.NAME + "='" + name + "'", null, null, null, null);
		cur.moveToFirst();
		int value = cur.getInt(0);
		cur.close();
		return value;
	}
	/**
	 * Delete a connection
	 */
	public void deleteConnection(int cid, String socName){
		String where = ConnectionTable._ID + " = ? ";
		_writableDB.delete(ConnectionTable.TABLE_NAME, where, new String[] {String.valueOf(cid)});
		FollowedPerson tempFol = (FollowedPerson) _socialNetworksWithConnections.get(socName).remove(cid);
		_allConnections.remove(tempFol);
	}
	public void updateConnection(FollowedPerson foll, String... columns ){
		ConnectionFactory fact = new ConnectionFactory();
		ContentValues vals = fact.turnToPartialDBInput(foll, columns);
		String strFilter = ConnectionTable._ID + "=" + foll.getId();
		_writableDB.update(ConnectionTable.TABLE_NAME, vals, strFilter, null);
	}
	/**
	 * Get connection by giving user id as parameter
	 * @param userid
	 * @return FollowedPerson. If there isn't any connections, return value is null
	 */
	public FollowedPerson getConnectionWithUserId(String userid, SocialNetKeys key){
		Cursor cur = _readableDB.rawQuery("SELECT * FROM " + ConnectionTable.TABLE_NAME + " WHERE " +
				ConnectionTable.USERID + "='" + userid + "' AND " +
				ConnectionTable.SOCIAL_AREA + " = '" + key.toString() + "'", null);
		ConnectionFactory confac = new ConnectionFactory();
		FollowedPerson temp = null;
		if(cur.moveToFirst())
			temp = (FollowedPerson) confac.turnToObj(cur);
		cur.close();
		return temp;
	}
	/********************************************
	 * INSERT CONNECTIONS
	 ********************************************/
	public int getNumOfTuplesInTableOfSocialNetwork(SocialNetworkNames.SocialNetKeys key){
		Cursor cur = _readableDB.rawQuery("SELECT COUNT(*) FROM "+ ConnectionTable.TABLE_NAME + " WHERE " + ConnectionTable.SOCIAL_AREA + " ='" +key.toString() + "'", null);
		cur.moveToFirst();
		int count= cur.getInt(0);
		cur.close();
		return count;
	}
	/**
	 * This method retrieves key-based connections from database. Then, it resets related variables in this class. 
	 * These are _allConnections and _socialNetworksWithConnections. Connections of other networks are not affected.
	 * @param key SocialNetKeys
	 */
	private void refreshConnections(SocialNetKeys key){
		String exceptSocialName="";
		switch(key){
			case ins:
				exceptSocialName = SocialNetworkNames.INSTAGRAM;
				break;
			case fa:
				exceptSocialName = SocialNetworkNames.FACEBOOK;
				break;
			case tw:
				exceptSocialName = SocialNetworkNames.TWITTER;
				break;
			case g_:
				exceptSocialName = SocialNetworkNames.GOOGLE;
				break;
			case f_sq:
				exceptSocialName = SocialNetworkNames.FOURSQUARE;
				break;
			default:
				Log.e(SRM_LOG_ERR, "A SocialNetKey must be given to refreshconnections method as parameter!");
				System.exit(0);
		}
		
		ArrayList<FollowedPerson> mylist = getConnectionsOfSocialNetworkFromDB(key);
		
		FollowedPerson tempUser;				//remove instagram connections
		for (int i = 0; i < _allConnections.size(); i++) {
			tempUser = _allConnections.get(i);
			if(tempUser.getConntype() == key){
				_allConnections.remove(i);
				i--;
			}
		}
		_allConnections.addAll(mylist);			//add all instagram connections freshly fetched from database
		
		/***********************/				//add these connections into hashmap to
		LinkedHashMap<Integer, AbstractHolder> tempHashMap = new LinkedHashMap<Integer, AbstractHolder>(mylist.size());
		for (int j = 0; j < mylist.size(); j++) {
			tempHashMap.put(mylist.get(j).getId(), mylist.get(j));
		}
		_socialNetworksWithConnections.put(exceptSocialName, tempHashMap);
	}
	public void insertListOfConnections(AbstractHolder[] mylist, SocialNetKeys key){
		switch(key){
			case ins:
				insertInstagramConnections((InstagramUser []) mylist);
				break;
			case f_sq:
				insertFoursquareConnections((FollowedPerson []) mylist);
				break;
			case tw:
				insertTwitterConnections((FollowedPerson []) mylist);
				break;
		}
		refreshConnections(key);
	}
	/**
	 * Insert Twitter connections
	 * @param mylist
	 */
	private void insertTwitterConnections(final FollowedPerson[] mylist){
		int numofconindb = getNumOfTuplesInTableOfSocialNetwork(SocialNetKeys.tw);
		int numtoinsert = mylist.length - numofconindb;
		ConnectionFactory confac = new ConnectionFactory();
		ContentValues values;
		FollowedPerson checkConn;
		for (int i = 0, k=0; (i < mylist.length) && (k < numtoinsert); i++) {
			checkConn =getConnectionWithUserId(mylist[i].getUserid(), mylist[i].getConntype());
			if(checkConn == null){		//if it's not in db, insert it
				values = confac.tunToDBInput(mylist[i]);
				_writableDB.insertOrThrow(ConnectionTable.TABLE_NAME, null, values);
				k++;
			}
		}
	}
	/**
	 * Insert Foursquare connections
	 * @param mylist FollowedPerson
	 */
	private void insertFoursquareConnections(final FollowedPerson[] mylist){
		int numofconindb = getNumOfTuplesInTableOfSocialNetwork(SocialNetKeys.f_sq);
		int numtoinsert = mylist.length - numofconindb;
		ConnectionFactory confac = new ConnectionFactory();
		ContentValues values;
		FollowedPerson checkConn;
		for (int i = 0, k=0; (i < mylist.length) && (k < numtoinsert); i++) {
			checkConn =getConnectionWithUserId(mylist[i].getUserid(), mylist[i].getConntype());
			if(checkConn == null){		//if it's not in db, insert it
				values = confac.tunToDBInput(mylist[i]);
				_writableDB.insertOrThrow(ConnectionTable.TABLE_NAME, null, values);
				k++;
			}
		}
	}
	/**
	 * Insert instagram connection
	 * @param mylist InstagramUser
	 */
	private void insertInstagramConnections(final InstagramUser[] mylist){
		ContentValues values;
		for (int i = 0; i < mylist.length; i++) {
			values = new ContentValues();
			values.put(ConnectionTable.NAME, mylist[i].getName());
			values.put(ConnectionTable.SOCIAL_AREA, SocialNetworkNames.SocialNetKeys.ins.toString());
			values.put(ConnectionTable.SELECTED, 0);
			values.put(ConnectionTable.USERID, mylist[i].getId());
			_writableDB.insertOrThrow(ConnectionTable.TABLE_NAME, null, values);
		}
	}
	/*********************************************/
	public ArrayList<FollowedPerson> getConnectionsOfSocialNetworkFromDB(SocialNetworkNames.SocialNetKeys key){
		Cursor cur = _readableDB.rawQuery("SELECT * FROM "+ ConnectionTable.TABLE_NAME + " WHERE " + ConnectionTable.SOCIAL_AREA + " ='" +key.toString() + "'", null);
		ArrayList<FollowedPerson> mylist = new ArrayList<FollowedPerson>(cur.getCount());
		if(cur.moveToFirst()){
			do{
				mylist.add(new FollowedPerson(cur.getInt(0), cur.getString(1), key, cur.getInt(3), cur.getString(4)));
			}while(cur.moveToNext());
		}
		cur.close();
		//set _socialNetworksWithConnections
		return mylist;
	}
	/*********************************/
	/**INSERT NOTIFICATION INTO DB**/
	/*********************************/
	public void insertNotification(SocialNetworkNames.SocialNetKeys key, FollowedPerson conn){
		switch(key){
			case ins:
				insertInstagramNot(conn);
				break;
			case tw:
				insertTwitterNotification(conn);
				break;
		}
	}
	private void insertInstagramNot(FollowedPerson conn){
		InstagramNotificationFactory insFactory = new InstagramNotificationFactory(conn.getId());
		ContentValues val;
		for(int j=0; j < conn.getNotificationList().size(); j++){
			val = insFactory.turnToDBInput(conn.getNotificationList().get(j));
			_writableDB.insertOrThrow(IInstagramNotificationTable.TABLE_NAME, null, val);
		}
	}
	private void insertTwitterNotification(FollowedPerson conn){
		TwitterNotificationFactory twFactory = new TwitterNotificationFactory(conn.getId());
		ContentValues val;
		for(int j=0; j < conn.getNotificationList().size(); j++){
			val = twFactory.turnToDBInput(conn.getNotificationList().get(j));
			_writableDB.insertOrThrow(ITwitterNotificationTable.TABLE_NAME, null, val);
		}
	}
	//RETRIEVE NOTIFICATIONS FROM DB
	public InstagramNotification[] getInsNotifications(){
		Cursor cur = _readableDB.rawQuery("SELECT * FROM " +IInstagramNotificationTable.TABLE_NAME, null);
		InstagramNotification[] nots = new InstagramNotification[cur.getCount()];
		InstagramNotificationFactory insFac = new InstagramNotificationFactory(0);
		int i=0;
		if(cur.moveToFirst()){
			do{
				nots[i] = (InstagramNotification) insFac.turnToObject(cur);
			}while(cur.moveToNext());
		}
			
		cur.close();
		return nots;
		
	}
	/**
	 * Just intagram notificaiotn count is calculated now. Edit this method
	 * @return
	 */
	public long getNotificationCount(){
		long count = DatabaseUtils.queryNumEntries(_readableDB, IInstagramNotificationTable.TABLE_NAME);
		return count;
	}
	/*******************************************************************/
	/*******************************************************************/
	/***********************GET-SET METHODS*****************************/
	/*******************************************************************/
	/*******************************************************************/
	public LinkedHashMap<String, LinkedHashMap<Integer, AbstractHolder>> socialNetworksWithConnections(){
		return _socialNetworksWithConnections;
	}
	public LinkedHashMap<Integer, AbstractHolder> getConnectionsOfSocialNetwork(String socialnetname){
		return _socialNetworksWithConnections.get(socialnetname);
	}
	public ArrayList<Group> outerCategories(){
		return _outerCategories;
	}
	public LinkedHashMap<Integer, Group> allCategories(){
		return _allCategories;
	}
	public ArrayList<FollowedPerson> allConnections(){
		return _allConnections;
	}
	public SQLiteDatabase getReadableDb(){
		return _readableDB;
	}
	public SQLiteDatabase getWritableDb(){
		return _writableDB;
	}
}
